﻿-- Table: ga.die

-- DROP TABLE ga.die;

CREATE TABLE ga.die
(
  --id serial NOT NULL,
  codigo_distribuidor integer NOT NULL,
  data_entrada date,
  nome character varying(128) NOT NULL,
  rg character varying(64),
  orgao_expeditor character varying(10),
  cpf character(11),
  data_nascimento date,
  sexo character(1),
  cep character(8),
  endereco character varying(160),
  bairro character varying(64),
  tel_comercial character varying(16),
  tel_residencial character varying(16),
  tel_celular character varying(16),
  email character varying(45),
  co_participante_nome character varying(128),
  co_participante_rg character varying(64),
  co_participante_orgao_expeditor character varying(24),
  co_participante_cpf character(11),
  co_participante_data_nascimento date,
  co_participante_cnpj character varying(14),
  co_participante_razao_social character varying(128),
  codigo_patrocinador integer,
  CONSTRAINT pk_die PRIMARY KEY (codigo_distribuidor ),
  CONSTRAINT fk_die FOREIGN KEY (codigo_patrocinador)
      REFERENCES ga.die (codigo_distribuidor) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
-- Index: ga.idx_die_nome

-- DROP INDEX ga.idx_die_nome;

CREATE INDEX idx_die_nome
  ON ga.die
  USING btree 
  (nome);

-- Index: ga.unq_die_cpf

-- DROP INDEX ga.unq_die_cpf;

CREATE UNIQUE INDEX unq_die_cpf
  ON ga.die
  USING btree
  (cpf);

CREATE UNIQUE INDEX unq_die_rg
  ON ga.die
  USING btree
  (rg);
  
CREATE UNIQUE INDEX unq_die_cnpj
  ON ga.die
  USING btree
  (co_participante_cnpj);

-----------------------------------------------------
-----------------------------------------------------
-- Table: ga.pontuacao_die

-- DROP TABLE ga.pontuacao_die;

CREATE TABLE ga.pontuacao_die
(
  id serial NOT NULL,
  codigo_die integer,
  dia integer,
  mes integer,
  ano integer,
  CONSTRAINT pk_pontuacao PRIMARY KEY (id ),
  CONSTRAINT fk_pontuacao_die FOREIGN KEY (codigo_die)
      REFERENCES ga.die (codigo_distribuidor) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

-- Index: ga.idx_pontuacao_iddie

-- DROP INDEX ga.idx_pontuacao_iddie;

CREATE INDEX idx_pontuacao_iddie
  ON ga.pontuacao_die
  USING btree
  (codigo_die );
  
 CREATE UNIQUE INDEX unq_pontuacao
  ON ga.pontuacao_die
  USING btree
  (codigo_die , mes , ano );

-----------------------------------------------------
-----------------------------------------------------
-- Table: ga.arvore_die

-- DROP TABLE ga.arvore_die;

CREATE TABLE ga.arvore_die
(
  codigo_die integer NOT NULL,
  mes integer NOT NULL,
  ano integer NOT NULL,
  nivel integer DEFAULT 0,
  qtd_filhos integer[] DEFAULT '{0,0,0,0,0,0,0,0}'::integer[],
  CONSTRAINT pk_arvore_die PRIMARY KEY (codigo_die , mes , ano ),
  CONSTRAINT fk_arvore_die FOREIGN KEY (codigo_die) REFERENCES ga.die (codigo_distribuidor)
);

--ideice para mes e ano?




-----------------------------------------------------
-----------------------------------------------------
-- DROP FUNCTION ga.insertPontuacaoDie();

CREATE OR REPLACE FUNCTION ga.insertPontuacaoDie() 
RETURNS TRIGGER AS $$ 
BEGIN
	insert into ga.pontuacao_die(codigo_die,dia,mes,ano) 
	values (new.codigo_distribuidor,
		extract(day from new.data_entrada),
		extract(month from new.data_entrada),
		extract(year from new.data_entrada));
	return new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tgInsertDie AFTER INSERT 
ON ga.die FOR EACH ROW
 EXECUTE PROCEDURE ga.insertPontuacaoDie();




-----------------------------------------------------
-----------------------------------------------------
--esquema public:

-- Function: retorna_endereco(character)

-- DROP FUNCTION retorna_endereco(character);

CREATE OR REPLACE FUNCTION retorna_endereco(IN character)
  RETURNS TABLE(cep character varying, endereco character varying, bairro character varying, cidade character varying, uf character varying) AS
$BODY$
	SELECT endereco.cep, endereco.endereco, bairro.bairro,cidade.cidade,cidade.uf
	FROM endereco INNER JOIN bairro ON endereco.id_bairro = bairro.id_bairro
	INNER JOIN cidade ON endereco.id_cidade = cidade.id_cidade
	WHERE endereco.cep=$1;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION retorna_endereco(character)
  OWNER TO postgres;

